Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Hi all
I have excel work book that consists of 60 similar worksheets labeled from 1 to 60 and one summary sheet
the summary sheet is 60 raw, each raw summarizes a sheet and labeled on the left with the label of the sheet
this is the viewX Y Z
1 Vlookup Vlookup Vlookup
2 Vlookup Vlookup Vlookup
.
.
.
60For raw no. 1 The lookup_value is the column label (x,y, or z) and searchs in sheet 1 and get the value
For raw no. 2 The lookup_value is the column label (x,y, or z) and searchs in sheet 2 and get the value
and so on
it's OK to fix the vlookup formula when dragging horizontally through the raw
but the problem is when dragging the formula vertically that the sheet name is not changing
Can any one help with this??
Instead of using the label, use the column reference.
Excel cannot know what your column labels are but can determine which column the formula is referring to.
HTH
Bryan
0 
The problem is not with the column,This is the formula that i use
=VLOOKUP(I$1,'1'!$F:$H,2,0)I$1 : the column header and the look_value and no problem with this,
'1'!$F:$H : is the table arraye in sheet no. 1
the problem here is with '1' (the sheet no.)
i want the sheet no. to change to '2','3',....,'60' when i drag the formula verticaly through the 60 raws
0
Try using ROW() to pick up the current row number that the formula is in. Use an offset if the current row doesn't match the sheet you are trying to reference.
e.g.
In any cell in Row 1 the formula =ROW() will return 1
In any cell in Row 3 the formula =ROW()-2 will also return 1.So if your first VLOOKUP is in Row 1, this will reference the sheet named 1:
=VLOOKUP(I$1,INDIRECT(ROW()&"!$F:$H"),2,0)
If your first VLOOKUP is in Row 16, this will also reference the sheet named 1:
=VLOOKUP(I$1,INDIRECT(ROW()-15&"!$F:$H"),2,0)
As you drag this down, the ROW() value will increment.
0
It works
but the promlem gets harder for me :)
each sheet of the 60 contains main info and 5 subinfo
In the summary sheet, for each sheet of the 60 there's 5 raws to summry the subinfo and 1 raw to summary the main info
so in the summery sheet now i have 360 rawsso can you help with this??
0
Oh, so what you originally asked for:
i want the sheet no. to change to '2','3',....,'60' when i drag the formula verticaly through the 60 raws
is not what you really want?
We can only offer advice based on what you ask us to help with. You haven't given us enough detail to help with your additional task.
BTW... it's row not raw.
0
Sorry for not providing enough details, yeah i really asked something at the first but the additional task was raised after that.
so are there any way to help me with that without linking the raw no. in the formulehere's the view in the summary sheet now
A B C D
1 x
2 - Vlookup
3 - ...........
4 - ......
5 - ....
6 - ..
7 1 Vlookup
8 - Vlookup
9 - ...........
10 - ......
11 - ....
12 - ..
13 2 Vlookup
So for for each sheet of the 60 there's 6 rows summary, 5 for subinfo and 1 main infoFor cell range (B1:B7) the the formula is =IFERROR(VLOOKUP(CONCATENATE($G2,I$1),'1'!$E:$H,3,0),0)
For cell range (B8:B13) the the formula is =IFERROR(VLOOKUP(CONCATENATE($G8,I$1),'2'!$E:$H,3,0),0)and the problem here is still with '1' and '2' and how to make the formula to tick to the sheet 60 summary without changing the sheet no. manualy
I hope this details in enough and sorry again,
Thanks in advance for your support
0
re: there's 6 rows summary
You say there are 6 rows for each sheet, but then you give a range of B1:B7 which is 7 rows, then B8:B13 which is 6. Please explain.
Maybe this will help....
Starting in B1 with this formula:
=ROUNDUP(ROW()/6,0)
I get 1's in B1:B6, 2's in B7:B12, etc.
I don't have 2007 so I can't test an IFERROR function, so going back to my original VLOOKUP suggestion, I would use something like this:
=VLOOKUP(I$1,INDIRECT(ROUNDUP(ROW()/6,0)&"!$F:$H"),2,0)
As before, an offset can be subtracted from ROW() if your formulae do not start in Row 1.
0
Thanks alot
It works
but i'm trying to understand the function of "INDIRECT" but i can'tThanks for support
0
As noted at the site given below, the Excel Help file doesn't do a very good job of showing the power of the INDIRECT function.
In the simplest of terms, the INDIRECT function tells Excel to use the argument as a reference, regardless if that argument is the contents of a cell, the result of another formula, a text string, etc. Depending on how you build the argument, it can reference a single cell, a range, a sheet, etc.
e.g. Put this in Row 1:
=INDIRECT(ROW()&"!$F:$H")
ROW() will evaluate to 1 which we then concatenate (&) with the text string "!$F:$H" to create a text string of:
"1!$F:$H"
INDIRECT converts this string into a reference so Excel actually sees the VLOOKUP formula as:
=VLOOKUP(I$1,1!$F:$H,2,0)
A simliar example, but using the contents of cells instead of a function would be something like:=INDIRECT(A1&"!B"&D1)
Whatever is in A1 would be picked up as a Sheet name and whatever is in D1 would be pickup as a Row value.
Let's say A1 contains the word Profits, either as the result of a function or as simple text.
Let's say D1 contained 44, either as the result of a function or as a simple number.
The INDIRECT function would return the same thing as =Profits!B44 would.
Obviously the difference is that =Profits!B44 is "static" but with INDIRECT, as you change the contents of A1 and/or D1, the result will change.
See here for more:
0
Hello
I used the formula in another way
Each row have a label in the left representing the sheet no. it summarizes
as per the below formula cell A7 contains the sheet name that i want it's summary
=VLOOKUP(I$1,INDIRECT($A7&"!$f:$h"),2,0)
anyway
i'm trying to use the formula the same way in another workbook but with SUMIF function
=SUMIF(INDIRECT($J$1&"!$G:$G"),$A2,INDIRECT(J1&"!$h:$h"))
Cell J1 contains the sheet name that i want to apply the SUMIF on it but the problem that cell J1 content is text and sheet name is the same text but the formula works only if this text is one word wthout a spaces
Exp : it works if the word is DVC but not if it's DVC ALL
Can you help with this?
0
=INDIRECT("'"&A1&"'!$A$1")
If you look carefully you will see a a couple of single quotes buried in there. Text References with spaces must be enclosed in single quotes.
Expanded for clarity, it looks like this:
(" ' " & A1 & " ' !$A$1 ") ^ ^
0
Glad to have been of assitance...and even happier that you adapted what I offered to fit your needs.
I'd rather offer concepts that can be adapted and modified than the final solution. Adapting means you learned something instead of just cutting and pasting and not understanding why the solution works.
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
