Hi,
Hoping to find a little help, I was hoping to have a formula in column A that will automatically return the header value in column C for all line items under each cost code.
Please see attached. I'm trying to think that a formula be like, if the cell is blank, return previous value. Or return header value under 'Ref'.
Thank you very much.
I expect your real data will be different, and refs will be actual numbers
=IF(OR(B5="ref",C5<>""),"",IF(C3<>"",C3,A5)) may work in a5
or
=IF(OR(C4<>"",C5<>""),"",IF(C3<>"",C3,A5))
In A4 drag copied down:
=IF(OR(B4="Ref",C4<>"",B4=""),"",LOOKUP(2,1/(B$4:B4="Ref"),C$3:C3))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
IN A5:
and copy down.Formula:
Please Login or Register to view this content.
Edit: Ali beat me to it!
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Hi Everyone,
Thank you for your formulas, it worked perfectly on my sheet, I just encountered some issues with my actual sheet as I realized there are some blank lines in between in column B so the tendency, the formula returns a blank cell too. Because of that I just thought maybe it would be more effective if we only use the "Ref" as reference and then the formula is supposed to return the value above it, see attached. So that in this case the formula only needs to find one criteria.
Hope you can help me still. Thank you very much!
EDIT :
Hello again,
I tried to twitch Ali's formula and I almost got it, however if you can again look at the edited version of my sheet, cell A11 and A14 should be blank however I could not find the right formula to make it blank too. Thank you all for the help!!!
Last edited by MyStix01; 11-25-2019 at 03:24 AM.
Changing the returned range in my formula:
In A5:and copy down.Formula:Please Login or Register to view this content.
Hi Olly,
This is perfect, thank you so much for the help. By the way, I figured that this formula works too.
=IFERROR(IF(OR(B5="Ref",B6="Ref",B5="Job Totals:"),"",LOOKUP(2,1/(B$4:B5="Ref"),B$3:B4)),"")
Thank you all for the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks