Showing New Worksheet

[Cellar Webs]

If anyone has any suggestions, thanks!

Here's my problem. I have a cell that contains the code: =Day1!G4. It reveals what is on another worksheet, but I want to be able to have the next cell be: =Day2!G4. Is there any way that I can code this without having to physically type in every cell? I tried, =Day1+1!G4 but it didn't like that.

Thanks for the help!

Cellar Webs

[oldchippy]

Just auto-fill your first formula, by dragging the bottom right corner of the cell where you see a black cross

[VBA Noob]

Hi,

Try

=INDIRECT("Day"&COLUMN(A1)&"!G4")

VBA Noob

[Cellar Webs]

Oldchippy, I tried yours and it changes the code from =Day1!G4 to =Day1!H4. So that doesn't work.

VBA Noob, I tried yours but it seems to copy the same worksheet for my total. The "Day1" stands for the worksheet I want it to grab information out of. Maybe if you had the time to explain some of the code you showed me, I could understand it better.

Thanks again and Merry Christmas!

Cellar Webs

[VBA Noob]

Take a look at this example.

Indirect mixes the Text and formulas to lokk at the sheets

=COLUMN(A1) you can use to return a number. Enter in a cell and drag right

VBA Noob

[oldchippy]

Oldchippy, I tried yours and it changes the code from =Day1!G4 to =Day1!H4. So that doesn't work.

VBA Noob, I tried yours but it seems to copy the same worksheet for my total. The "Day1" stands for the worksheet I want it to grab information out of. Maybe if you had the time to explain some of the code you showed me, I could understand it better.

Thanks again and Merry Christmas!

Cellar Webs

Sorry Cellar Webs - misread the question, perhaps better luck next time - Merry Christmas

[Cellar Webs]

VBA Noob,
I tried messing around with the example you provided, and thank you by the way, but I couldn't understand how it works. I think I'm slowly starting to understand it, but I can't figure it out. If you have the time, could you explain what each part of the code is doing in the cell? Thanks for the help.

Cellar Webs

[VBA Noob]

No problem

In A1 you have

=INDIRECT("Day"&COLUMN(A4)&"!G"&COLUMN($D$4))

Is uses the indirect function.

It uses the Word "Day" as part of the sheet name then the sheet number COLUMN(A4) returns 1 when you drag it across it increases by 1 to give you the sheet name e.g Day1, Day2 etc

This part of the formula enters the symbol for a sheet and the Column letter &"!G"&

Finally COLUMN($D$4) returns 4 so when added to the G is always looks at cell G4 on all sheets

HTH

VBA Noob

[Cellar Webs]

VBA Noob,
Thanks, that helps a lot. Now... let's see if I can get it to work.

Cellar Webs

[Cellar Webs]

VBA Noob,
Here's an example of what I'm working with. I can't seem to figure out how to have it import a number from a different cell on the same worksheet. For example: It's grabbing G4 right now, what do I need to change in the code to have it grab G6, or any other cell that I want? Thanks for the help.

Cellar Webs

[VBA Noob]

Hi,

Try

=OFFSET(INDIRECT("Day"&COLUMN(A2)&"!$G$6"),ROW(A1)-1,0)

or

=INDIRECT("Day"&COLUMN(A2)&"!G"&ROW(A6))


VBA Noob

[Cellar Webs]

VBA Noob,
It works great! Thank you so much.

Cellar Webs

[VBA Noob]

Glad it helped

Thx for the feedback

VBA Noob