scrappert
Prolific Pea
RefuPea #2956
Posts: 7,760
Location: Milwaukee, WI area
Jul 11, 2014 21:20:09 GMT
|
Post by scrappert on Apr 10, 2024 20:03:20 GMT
I have a vlookup formula pulling from one sheet to the next (pulling from sheetA to sheetB). But I have added another cell in sheetA-cell A2 (month that will change) and I want it to work so once I change the month, it will pull the data from sheetA into the correct month column in sheetB. SheetA will be a working sheet, data will be changed each month.
Formula in SheetB: =IFERROR(VLOOKUP($C7,SheetA$B$4:$D$107,3,FALSE),0)
How do I have it look at the month (A2)?
I hope I explained that good enough.
|
|
|
Post by ameslou on Apr 11, 2024 2:58:57 GMT
So if Sheet 1, cell A2 is say, February, you want it to populate the February column on Sheet 2?
I think this is an INDEX MATCH combo formula. I have an example I can post tomorrow once I’m back at my computer.
|
|
|
Post by jennyap on Apr 11, 2024 9:11:45 GMT
If I'm understanding right, you want the results of the formula you have posted to appear in just one of 12 columns on Sheet B, depending on the month?
Assuming you have the month names as the column headings, you could do that with a nested if formula (I'm assuming your column headings Jan-Dec are in cells A1-A12, adjust as needed)
=IF(A$1=SheetA!$A$2,(IFERROR(VLOOKUP($C7,SheetA!$B$4:$D$107,3,FALSE),0),"")
Use that in all 12 monthly columns, if I've got this right it should pull in your data as before in the correct column and give you a blank result in the other 11 columns.
That said, I'm not sure I entirely understand how useful this is. You say SheetA will change every month, but then SheetB will change too: once you change the month in SheetA, the new month column in SheetB will be populated, and the old month column will be blanked out, so you will only ever have the results in one column. If you're intending to fill all month columns in SheetB over time, you will need to break the lookup link for the current active month somehow once the data is complete but before you change the month on SheetA (eg by copy/pasting values in that column)
|
|
scrappert
Prolific Pea
RefuPea #2956
Posts: 7,760
Location: Milwaukee, WI area
Jul 11, 2014 21:20:09 GMT
|
Post by scrappert on Apr 11, 2024 12:36:57 GMT
That said, I'm not sure I entirely understand how useful this is. You say SheetA will change every month, but then SheetB will change too: once you change the month in SheetA, the new month column in SheetB will be populated, and the old month column will be blanked out, so you will only ever have the results in one column. If you're intending to fill all month columns in SheetB over time, you will need to break the lookup link for the current active month somehow once the data is complete but before you change the month on SheetA (eg by copy/pasting values in that column) SheetB will have all months showing, the old months will still show their data. SheetA is used to paste a report into so I can get the data into sheetB without having to manually do it. And I think I will have to do the copy and paste values in the other months if this does what I want. I will check this formula out.
|
|
scrappert
Prolific Pea
RefuPea #2956
Posts: 7,760
Location: Milwaukee, WI area
Jul 11, 2014 21:20:09 GMT
|
Post by scrappert on Apr 11, 2024 12:38:22 GMT
So if Sheet 1, cell A2 is say, February, you want it to populate the February column on Sheet 2? I think this is an INDEX MATCH combo formula. I have an example I can post tomorrow once I’m back at my computer. Yes, this is exactly what I want it to do. Sheet 2 will show all months.
|
|
CeeScraps
Pearl Clutcher
~~occupied entertaining my brain~~
Posts: 3,825
Jun 26, 2014 12:56:40 GMT
|
Post by CeeScraps on Apr 11, 2024 13:10:24 GMT
Bless you......to me you're speaking Greek............
I'm glad someone could help you!
|
|