Can I autofill worksheet name which is a number

[gardengal444]

I cannot figure out a way to auto-fill any of these cells that reference another worksheet.

This is a workbook with 1-31 worksheets for every day of the month and this page is the monthly recap.

When I drag any cell to auto-fill, the series is only the referenced CELL part (in B16, for example, the H17 becomes H18, but I want the '1' to become '2' and the H17 to be static.

Oops, as I am writing this, I am now reminded that I probably need a "$" somewhere in the J17. (J$17? J$17$?) But the sheet number(name) still will not increment. That will still be painful to manually edit when necessary.

Is there any way to make that happen? No macro's for me. novice here as you can surmise. regular UI only.

fwiw, the column references are not in my control. This originates from a daily CSV external download. I had this all working fine with a template (actually, a sample/model) that I had crafted once. But they just changed the download format on me! So I have to redo everything. Ouch!! And they did it in the middle of the month, so I won't even be able to use February/MonthlyTotals as a skeleton. THey may do it again so I need to be prepared. //end-vent

p.s. I just noticed I could normalize col D by referencing col D, so that only leaves two columns to deal with.

 

[JoeMo]

If you include a helper column (column B below) and change column A in a way that will autofill you can return the contents of a sheet like '15'. In the example below, Cell H17 on sheet '15' contains the text "Hello".

Excel Workbook
	A	B	C
1	Prefix	Ref Cell
2	'1'!	H3
3	'2'!	H4
4	'3'!	H5
5	'4'!	H6
6	'5'!	H7
7	'6'!	H8
8	'7'!	H9
9	'8'!	H10
10	'9'!	H11
11	'10'!	H12
12	'11'!	H13
13	'12'!	H14
14	'13'!	H15
15	'14'!	H16
16	'15'!	H17	Hello
Sheet1

 

[gardengal444]

Thanks, that looks intriguing, I'll have to try that and see.

I don't understand your forumula in A16 (yet), but even if I had to enter that column once that would be okay. The "prefixes" would never have to be entered again because they would always only be referenced. I don't quite understand if the column A values are text or a reference to the whole sheet. I'll get a better feel for it as I play around.

 

[JoeMo]

Thanks, that looks intriguing, I'll have to try that and see.

I don't understand your forumula in A16 (yet), but even if I had to enter that column once that would be okay. The "prefixes" would never have to be entered again because they would always only be referenced. I don't quite understand if the column A values are text or a reference to the whole sheet. I'll get a better feel for it as I play around.

If you enter the formula as shown in A2 and pull it down it should autofill so cell A16 for example would read '16'!

 

[gardengal444]

Got it working. This is what I actually needed in the resultant cell:

=INDIRECT(B2&"$H$17")

I need the helper column for B2 (sheet name), but the other part is a constant cell in the referenced sheet.

I'm assuming the same concept will be adaptable to my other cells even though they have more stuff in them.

And I can even hide the helper column just to reduce clutter.

Happy camper here!! Forums rule!! Thanks so much.