Forum Discussion
LOOKUP FORMULA BASED ON HEADERS
Is this something you think would work here?
It's not altogether clear what "this" refers to. Presumably the part of the formula that refers to headers.
I have tried using the formula but have not had any luck. Basically we just need to copy these items across to another sheet but due to the columns not lining up (hidden columns in the top sheet throwing out the basic formula), it is causing some issues.
I have no doubt that something could work, but it would be a lot easier to figure it out if we could see the actual spreadsheets (or, if actuals contain confidential data, a mockup of them containing false data but representing the situation accurately. Post copies on OneDrive or GoogleDrive and paste a link here that grants access.
Seeing the actual helps in understanding the bigger picture--how data are organized, etc--and because Excel always has multiple effective routes from A to B, the bigger picture helps determine which route (which function, which formulas) make most sense.
Ok see the attached sheets. Basically trying to do the below.
QLDMaintenance>P94 to equal RevenueALL>AU109
Then
QLDMaintenance>V94 to equal RevenueALL>AV109
and so on
As columns have been added the the QLDMaintenance sheet, it is difficult to simply copy/paste the formula so was hoping to use the Header option but as you said, I could be overthinking it (which I do tend to do...).
Thanks
Chris -- after looking at these two workbooks, I'm somewhat overwhelmed. For the specific task you're asking about--even recognizing that the idea is to get those numbers all the way down to column CA in the "RevenueAll" sheet--I would suggest you not try to write a single formula that can be copied. With both workbooks open at the same time, it was easy to just enter the equal sign in the target sheet, cell AU109, click on the source sheet, and end up with
='[QLDMainteance.xlsx]QLD | Forecast'!$P$95
You could do the same steps for each of the 24 or 36 or 48 cells and be done. It would take a little bit of time, but no more than you've already spent looking for a single formulaic approach.
That said, though, here's my real concern: your overall design contains SO MANY redundancies, SO MANY cells referring to cells in other columns or other rows and simply bringing over the value from that source, which brought it over from yet another, which brought it over from yet another...and so on ad infinitum. Well, not quite ad infinitum, but you get the point, I hope.
And now you're trying to do the same--bring over a number that's already been brought forward multiple times, just, this time, into another workbook.
It's fundamentally, in my opinion, not a good overall design.
It may work.
But it has the appearance of an Excel spreadsheet that was designed based on an extensive paper-based ledger sheet accounting and project management process. That is, it uses Excel to add, subtract, multiple--do basic math--but it's incredibly labor intensive, very inefficient, in doing so. So it would appear.
I realize this could be an erroneous impression. But if we were sitting down face-to-face, I'd be wanting to learn more what all the inputs are, then the outputs (reports, etc), and would then be suggesting that you find a good spreadsheet designer who could help you take advantage of Excel's marvelous abilities to take well organized (i.e., non-redundant) raw data at the input end, and extract/slice/dice/combine for useful output. (By the way, that would not be me; I'm long retired, neither looking for nor needing extra work.)
It's not advice you were looking for, and you are certainly not obligated to take it. I can't in good conscience, though, not give you that feedback.