dragging formula with a sheet name

June 3, 2009 at 02:38:43
Specs: Exceel 2007
 Hi allI have excel work book that consists of 60 similar worksheets labeled from 1 to 60 and one summary sheetthe summary sheet is 60 raw, each raw summarizes a sheet and labeled on the left with the label of the sheetthis is the view X Y Z1 Vlookup Vlookup Vlookup2 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 valueFor raw no. 2 The lookup_value is the column label (x,y, or z) and searchs in sheet 2 and get the valueand so onit's OK to fix the vlookup formula when dragging horizontally through the rawbut the problem is when dragging the formula vertically that the sheet name is not changingCan any one help with this??

See More: dragging formula with a sheet name

#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.HTHBryan

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. 1the 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 1In 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 worksbut the promlem gets harder for me :)each sheet of the 60 contains main info and 5 subinfoIn the summary sheet, for each sheet of the 60 there's 5 raws to summry the subinfo and 1 raw to summary the main infoso 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 rawsis 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 formulehere's the view in the summary sheet now A B C D1 x 2 - Vlookup 3 - ........... 4 - ......5 - ....6 - ..7 1 Vlookup 8 - Vlookup 9 - ........... 10 - ......11 - ....12 - ..13 2 VlookupSo 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. manualyI 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 summaryYou 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 alotIt worksbut i'm trying to understand the function of "INDIRECT" but i can'tThanks 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:

Report •

#10
June 9, 2009 at 04:24:51
 HelloI used the formula in another wayEach row have a label in the left representing the sheet no. it summarizesas 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)anywayi'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 spacesExp : it works if the word is DVC but not if it's DVC ALLCan 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 •