• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need help with long hlookup formula

T

topher5832

New Member
Hello Everyone.. This is my first post ever on Chandoo.org and from what Im seeing so far this looks like the place to go for excel help. Here goes my question


I have 60-70 very lengthy Hlookup formulas in a spreadsheet in doing for work. I was wondering if there is a way to change the "row index number" on all of my lookups in a single cell at once. Each one of my employees is a different "row index number" and having to change it 6-8 times per employee for 60 employees is very time consuming.


=(HLOOKUP($B$2,'Monthly Units'!$E$1:$P$48,5,FALSE)+HLOOKUP($C$2,'Monthly Units'!$E$1:$P$48,5,FALSE)+HLOOKUP($D$2,'Monthly Units'!$E$1:$P$48,5,FALSE))/(HLOOKUP($B$2,'Monthly Hours'!$E$1:$P$48,5,FALSE)+HLOOKUP('Central East'!$B$2,'Monthly Indirect'!$E$1:$P$48,5,FALSE)+HLOOKUP('Central East'!$C$2,'Monthly Hours'!$E$1:$P$48,5,FALSE)+HLOOKUP('Central East'!$C$2,'Monthly Indirect'!$E$1:$P$48,5,FALSE)+HLOOKUP($D$2,'Monthly Hours'!$E$1:$P$48,5,FALSE)+HLOOKUP($D$2,'Monthly Indirect'!$E$1:$P$48,5,FALSE))


Each employee has 5 cells that contain formulas similar to this. I would really like to be able to change all of the index numbers (the number 5 in this case) at the same time. Does anyone know if this is possible? any help would be greatly appreciated
 
not sure that i understood your question correctly, my english is not very good (

but maybe you could place "row index" in some cell

[pre]
Code: 
----+-------------+----------
|      AA     |   AB
----+-------------+----------
1  |  employee1  |  5
2  |  employee2  |  6
3  |  employee3  |  7
[/pre]
and then use value of this cell in your formulas:


=(HLOOKUP($B$2,'Monthly Units'!$E$1:$P$48,$AB1,FALSE)...
 
I guess I was all over the place with my post. the way the syntax reads is hlookup(lookup_value,table_array,row_index_num,[range_lookup].. I think.. The way I set up my data sheet is each row has a employee name is associated with a row. so employee John Doe is row number 7.. I have very lengthy lookup formulas that now all have the "row_index_num" part of the syntax as the number 1. I wrote the formula once and copied it to every other employee. Now I have to change that part of the syntax for all 60 employees. My question is because my lookups are so lenghty and have multiple lookup syntax's in them which in turn means multiple "row_index_num" is there a way to change every "row_index_num in every syntax of the formula at once time vs changing each syntax one by one.
 
definitely there is some way to rewrite formulas by vba macros

but i think that i not really understand your problem


and i wonder why you use only absolute cell references?

i suspect that maybe relative references can replace all this HLOOKUPs
 
I would change the 5 to a match based on the name in Column 1 etc


But if there is so many have that value as a hidden column next to the name so it is only looked up once but refereed to via the reference instead of being fixed
 
You must log in or register to reply here.
Back
Top