Author Topic: Excel help  (Read 3238 times)

0 Members and 1 Guest are viewing this topic.

Online Slateman

  • Posts: 41718
Re: Excel help
« Reply #50: June 01, 2016, 12:25:17 PM »
So, I think, maybe, I'm on the right path

Started with this Macro:
Quote
Sub cond_copy()

Dim tfCol As Range, Cell As Object

Set tfCol = Range("J5:J100")

For Each Cell In tfCol

If IsEmpty(Cell) Then
Exit Sub
End If

If Cell.Value = "Yes" Then
Cell.EntireRow.Copy
Sheet2.Select
ActiveSheet.Range("A65536").End(xlUp).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
End If
Which did what I wanted to, but when I ran it again, it simply kept copying the same data underneath. What I want it to do is overwrite the data each time I run the Macro

Someone suggested this: 
Quote
Sub cond_copy()
 Dim tfCol As Range, Cell As Object
 Set tfCol = Range("J5:J100")
    For Each Cell In tfCol
        If Cell.Value = "" Then
            Exit Sub
        End If
        If Cell.Value = "Yes" Then
            Cell.EntireRow.Copy Sheet2.Range("A2")
        End If
    Next
 End Sub

But now it only copies the last row from Sheet 1

Any ideas?

Offline dracnal

  • Posts: 1007
Re: Excel help
« Reply #51: November 18, 2016, 10:47:19 AM »
My turn with the weird Excel questions. I have a client who runs an oilchange business. Each day he has to download a master spreadsheet of data that covers all his stores, plus one spreadsheet for each store. He then has to cut and paste data from these downloaded spreadsheets into yet another spreadsheet to create his daily reports and Scoreboard.

I'm sure there is a way to automate this process. I suspect someone skilled could get it to be pretty much a single press macro button in an Excel sheet that goes out, automatically captures the data and gets it into the spreadsheet. I'm not that guy. For those of you that do this sort of thing, how hard is what I'm describing? Is it something that someone would be interested in freelancing?

Any pointers would be helpful - thankfully my client gets, at least conceptually, that this is totally out of the scope of work of our support contract and I'm giving a best effort attempt at this one.

Offline BrandonK

  • Posts: 8170
  • #LOLNats
Re: Excel help
« Reply #52: November 18, 2016, 11:18:54 AM »
Why does a simple data connection not work?

Offline BrandonK

  • Posts: 8170
  • #LOLNats
Re: Excel help
« Reply #53: November 18, 2016, 11:21:05 AM »
Try it. On the ribbon, click 'Data', then 'Get External Data' and select the source. The reports, if they feed from that sheet, should also update.

Offline comish4lif

  • Posts: 2841
  • Too Stressed to care.
Re: Excel help
« Reply #54: November 18, 2016, 11:30:55 AM »
If all of the data is on the Daily Master, can't he just create his reports from that dataset, there's no reason to cut that data and move it somewhere else....


Offline dracnal

  • Posts: 1007
Re: Excel help
« Reply #55: November 18, 2016, 11:35:09 AM »
Quote from: BrandonK on Today at 11:21:05 AM

    Try it. On the ribbon, click 'Data', then 'Get External Data' and select the source. The reports, if they feed from that sheet, should also update.


Since the source is an XLSX file, do I need to convert it to a table first in Excel? I don't see 'Excel Spreadsheet' as a data source in the Get External Data options.

If all of the data is on the Daily Master, can't he just create his reports from that dataset, there's no reason to cut that data and move it somewhere else....

Has to do with corporate protocol. The sheet he creates the reports from is locked and uneditable with the exception of a small handful of fields. He has a PDF instruction on what columns to cut and paste into which fields on what spreadsheet (of 7 sheets) and the thing automatically populates the reports that he has to print. Unfortunately, with the protection the formulas and what gets pulled from where is obscured. I'm just not a database / Excel guy; much happier with servers, Exchange and routing, so I don't have a clue how to best deal with this.

Online Slateman

  • Posts: 41718
Re: Excel help
« Reply #56: May 23, 2017, 04:33:17 PM »
Le bump


So I want to color a row, up until a particular cell in the event that a cell is blank within 2 day before an event


So A1 has a date. A2 a description. A3 has an empty cell

If today is 3 days or sooner of the date in A1, and A3 is blank, then I want cells A1, A2, and A3 to be shaded red.

How do I do this?

Online HalfSmokes

  • Posts: 18760
Re: Excel help
« Reply #57: May 23, 2017, 05:20:55 PM »
I tried to do something similar - if a1 held data, but b1 didn't, b1 would be a certain color- I gave up

Offline tomterp

  • Global Moderator
  • ****
  • Posts: 31944
  • Hell yes!
Re: Excel help
« Reply #58: May 23, 2017, 09:52:59 PM »
Le bump


So I want to color a row, up until a particular cell in the event that a cell is blank within 2 day before an event


So A1 has a date. A2 a description. A3 has an empty cell

If today is 3 days or sooner of the date in A1, and A3 is blank, then I want cells A1, A2, and A3 to be shaded red.

How do I do this?

Home > Conditional formatting > New Rule

You may need to do some math in a cell to meet your rules but you can nest the Conditional Formatting into an If statement to highlight the cell you want.   

Online GburgNatsFan

  • Posts: 17817
Re: Excel help
« Reply #59: May 23, 2017, 10:15:14 PM »
If you can write the formula, you can use conditional formatting (I know, not helpful.)

Select the first column's cell in a dummy row. Select Conditional Formatting, Highlight Cell Rules, More Rules, then select Use a formula to determine which cells to format. Type the formula, then select the format with the Format... button. Pick red highlighting.

Do the same for the second column's cell, then the third.

Then you can select all three cells and do Copy, then Paste Special... Formats to the real data.

Probably not clear, but I tested it with a very simple formula, =IF($C1=$A1, true). If you can write the formula, you can get the highlighting to work.


Le bump


So I want to color a row, up until a particular cell in the event that a cell is blank within 2 day before an event


So A1 has a date. A2 a description. A3 has an empty cell

If today is 3 days or sooner of the date in A1, and A3 is blank, then I want cells A1, A2, and A3 to be shaded red.

How do I do this?

Online Slateman

  • Posts: 41718
Re: Excel help
« Reply #60: May 25, 2017, 07:30:43 AM »
Yea, I knew about conditional formatting. I can't figure out how to write the forumla

Offline Mathguy

  • Posts: 7216
  • Whoa That Was A Good One ! Poke His Brain Here !
    • Outer Banks Beach House
Re: Excel help
« Reply #61: May 25, 2017, 09:46:17 AM »
What's the formula you are trying to write ?

Yea, I knew about conditional formatting. I can't figure out how to write the forumla

Online GburgNatsFan

  • Posts: 17817
Re: Excel help
« Reply #62: May 25, 2017, 10:32:26 AM »
=DATEDIF(B2,C2,"d") will return the difference in number of days ("d") between the date values in B2 and C2. It should be possible.

And =TODAY() in a cell will always return today....

You might need to cludge it, but it should be do-able (add an extra hidden column with the numeric calculation)...