Excel help - display the result of a formula
Discussion
Right, I've asked this on 'Mr Excel' so I'll see if they can help. But, need to get this working ASAP.
In a spreadsheet I have built up I have to read a damage estimate from a table based on flood level. To do this I'll use a simple Hlookup, easy enough.
But, my tables are built of 6 tabs, detached, semi, flat etc, then in each one these are split further by social class and propery age.
What I have done, I'm sure there is a better way, is use various if statements etc to build suitable results then use these, using the concatenate, to build the Hlookup.
This leave me with something like this -
=CONCATENATE("=","Hlookup(",BK2,",","","'",$BB2,"'","!",$BE2,$BH2,":",$BF2,$BI2,",",DJ$1,",TRUE)")
which, results in this - =Hlookup(0.139016,'Detached_age_social class'!B04:AT11,3,TRUE) (this is correct and give me the answer I want, so I know it works)
Now, I have this concatenate equation in 12 colums and going down proabably about 500 rows. So, my problem is this.
How can I make excel change them all to the result.
If I copy and paste special it just says the =hlookup(.......) and not the result, leaving me to have to select and press enter in each.
Likewise with f2, f9.
I tried a macro - set the relative reference, f2, f9 - move along, etc
move to start of next row. Then stop, new maco, play old one and it just lost what is was doing.
Any help please.
In a spreadsheet I have built up I have to read a damage estimate from a table based on flood level. To do this I'll use a simple Hlookup, easy enough.
But, my tables are built of 6 tabs, detached, semi, flat etc, then in each one these are split further by social class and propery age.
What I have done, I'm sure there is a better way, is use various if statements etc to build suitable results then use these, using the concatenate, to build the Hlookup.
This leave me with something like this -
=CONCATENATE("=","Hlookup(",BK2,",","","'",$BB2,"'","!",$BE2,$BH2,":",$BF2,$BI2,",",DJ$1,",TRUE)")
which, results in this - =Hlookup(0.139016,'Detached_age_social class'!B04:AT11,3,TRUE) (this is correct and give me the answer I want, so I know it works)
Now, I have this concatenate equation in 12 colums and going down proabably about 500 rows. So, my problem is this.
How can I make excel change them all to the result.
If I copy and paste special it just says the =hlookup(.......) and not the result, leaving me to have to select and press enter in each.
Likewise with f2, f9.
I tried a macro - set the relative reference, f2, f9 - move along, etc
move to start of next row. Then stop, new maco, play old one and it just lost what is was doing.
Any help please.
I'm not an Excel guru but I can see that you're concatenating two strings together to make one other string, which is why it's not calculating the actual result properly. It can't calculate the formula of a string. it has to be of the 'data type' of a formula.
I don't think there is an Eval(string) function so you might just have to work out how to do the formula without using Concatenate.
I don't think there is an Eval(string) function so you might just have to work out how to do the formula without using Concatenate.
Edited by zac510 on Wednesday 7th April 10:07
Just tried those and nothing.
I understand about the concatenate and string thing, but, copy and paste special works and creates the equation I desire.
If I select it and hit return, ie go into each cell individually, it works, just I can't be bothered to do that probably 4000 times, have to do this for 2 sites.
I understand about the concatenate and string thing, but, copy and paste special works and creates the equation I desire.
If I select it and hit return, ie go into each cell individually, it works, just I can't be bothered to do that probably 4000 times, have to do this for 2 sites.
I am trying to understand the problem you have here (outside of the copy and paste issue)
Is this correct?
You have a master sheet that works out all the stuff from other sheets that have the data on them.
Based on the data on the master sheet you will lookup the data from different sheets. E.g. If it is a detached house you look on the detached sheet, if it is a flat then you look on the flat sheet (I appreciate those examples may not be spot on, but that is the principal)?
Thus you have created a formula so that you can choose the correct sheet based on data on the master sheet, thus creating this concatenated formula?
Is this correct?
You have a master sheet that works out all the stuff from other sheets that have the data on them.
Based on the data on the master sheet you will lookup the data from different sheets. E.g. If it is a detached house you look on the detached sheet, if it is a flat then you look on the flat sheet (I appreciate those examples may not be spot on, but that is the principal)?
Thus you have created a formula so that you can choose the correct sheet based on data on the master sheet, thus creating this concatenated formula?
I am assuming this is correct so this is how I would do it
I would add an extra sheet called Lookup into the workbook and in that I would hold the references of where the data is like so:
Now this table holds where your lookup data exists and thus if you add more data then add them to this table or extend the range
Then on your master sheet I would use this lookup to get the range of where to do the HLOOKUP so the finished formula would be:
I will break that down.
We are using the INDIRECT function which does more or less what you are trying to do in that it allows you to create a range from a string.
We use the VLOOKUP to get the start of the range from the lookup table, and then again to find the end of the range. This lookup is based on the type of building, so has to match the lookup table.
Then we now have where to lookup the value (B6) on the correct worksheet
Hope that makes sense
I would add an extra sheet called Lookup into the workbook and in that I would hold the references of where the data is like so:
| Type | StartRange | EndRange |
|---|---|---|
| Detached | Detached!A1 | E2 |
| Flat | Flat!A1 | E2 |
Now this table holds where your lookup data exists and thus if you add more data then add them to this table or extend the range
Then on your master sheet I would use this lookup to get the range of where to do the HLOOKUP so the finished formula would be:
=HLOOKUP(B6,INDIRECT(VLOOKUP(A6,Lookup!$A$1:$C$3,2,FALSE) & ":" & VLOOKUP(A6,Lookup!$A$1:$C$3,3,FALSE)),2,TRUE)
I will break that down.
We are using the INDIRECT function which does more or less what you are trying to do in that it allows you to create a range from a string.
We use the VLOOKUP to get the start of the range from the lookup table, and then again to find the end of the range. This lookup is based on the type of building, so has to match the lookup table.
Then we now have where to lookup the value (B6) on the correct worksheet
Hope that makes sense
As above use the & to return a series of results in one cell.
i.e.
&" : "& acts to sperate the return of each lookup
=Hlookup(...)&" : "&Hlookup(....)&" : "& etc
If you have any numercial formating in the returns such as % you'll need to build this in as it will only show the numercial value.
If it was 75.67689% this would show as 0.756789 and if you wanted to show it to 2dp then you'd have to get quite complicated.
eg.
&" : "&(left(Hlookup(...)*100),4)&"% ")
i.e.
&" : "& acts to sperate the return of each lookup
=Hlookup(...)&" : "&Hlookup(....)&" : "& etc
If you have any numercial formating in the returns such as % you'll need to build this in as it will only show the numercial value.
If it was 75.67689% this would show as 0.756789 and if you wanted to show it to 2dp then you'd have to get quite complicated.
eg.
&" : "&(left(Hlookup(...)*100),4)&"% ")
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff