Hello
I have a table which I want to make a lookup in which looks like this :
Table 1
Sales
30Kg 300
40Kg 0
50Kg 200
Cost
30Kg 0
40Kg 400
50Kg 500
....
And a table from which I want to make the lookup from:
30Kg
Cost-------- Sales
x -------------- y
Now, I want to make a lookup in table 1 where it match Cost and Sales and set x = 0 and y = 300
Can I somehow make a lookup in a lookup to achieve this?
Can you not just do 2 separate VLOOKUP formula?
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
I would prefer doing it in one, as I have many more fields (eg different cost types) than shown in the exampleOriginally Posted by Domski
Do you think that is possible?
naquer, this is not very clear. Can you post an Excel file with some sample data and mock up the expected result?
Absolutely, and thanks for taking the time to help.
let me fast describe what I want to achieve again.
I have a sheet with my clients budget, where I in the merged sheet have the budgeted route in column A.
Now, this budget is also separated in weight (Kg)
As displayed in the merged sheet, I want to make a combination of some cells from the underlying sheets for every route separated on weight.
I demonstrated this with the 30 and 40 Kg from column T to AB.
I got the figures from the underlying sheet by manually linking to it, but as I have many routes, and many sheets I would prefer some kind of automatic process.
Some of the columns from the underlying sheets are fixed (the C1-C3) and Add1(C ), where the others are based on the given weight.
Any suggestions to a smart way of achieving this are more than welcome, and don’t hesitate to write if you need more information
Last edited by naquer; 03-30-2010 at 07:40 AM.
naquer, your DE sheet shows only DE data for the cities, right? Where would the NL data be? In a sheet called "NL"?
I would consider setting your data out in a format that is easier to work with but maybe less pleasing on the eye.
You can always create more visually pleasing summaries/tables from that data if needed.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Domski, I second that!
Thanks for the responses. I didn't include the NL sheet. However it looks similar to the DE sheet, only for NL data.
Can you give an example ( just a sample row) of how you would suggest setting up the data?
naquer, that's just the thing: the way the source data is laid out, each formula would have to be an individual concoction of Index/Match or some such.
If your data were available in a simple table layout, with one record per row, it would be much easier.
Your data is presented in a report style. This does not lend itself easily to further manipulation.
Data should be organised in a simple table.
Views and reports can then easily be created. But not the other way round.
Something like the attached would be what you would be looking at.
A layout like that is then very easy to query and summarise.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Great idea.
Now, this makes sense if made from the underlying sheet.
I have over 50 sheets, so do you know a fast macro to merge these?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks