dragging formula with a sheet name

June 3, 2009 at 02:38:43
Specs: Exceel 2007
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 view

X Y Z
1 Vlookup Vlookup Vlookup
2 Vlookup Vlookup Vlookup
.
.
.
60

For 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??


See More: dragging formula with a sheet name

Report •


#1
June 3, 2009 at 05:53:15
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


Report •

#2
June 3, 2009 at 06:03:00

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



Report •

#3
June 3, 2009 at 07:25:32
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.



Report •

Related Solutions

#4
June 3, 2009 at 08:26:46
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 raws

so can you help with this??


Report •

#5
June 3, 2009 at 09:59:24
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.


Report •

#6
June 4, 2009 at 00:40:36
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 formule

here'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 info

For 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


Report •

#7
June 4, 2009 at 06:33:52
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.


Report •

#8
June 6, 2009 at 22:52:27
Thanks alot

It works
but i'm trying to understand the function of "INDIRECT" but i can't

Thanks for support


Report •

#9
June 7, 2009 at 07:09:52
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:

http://www.cpearson.com/excel/indir...


Report •

#10
June 9, 2009 at 04:24:51
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?


Report •

#11
June 9, 2009 at 05:57:36
=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 ")
    ^                  ^


Report •

#12
June 9, 2009 at 07:25:08
Thanks you
It works

Report •

#13
June 9, 2009 at 07:35:19
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.


Report •


Ask Question