CODEWITHSUNDEEP
excel
user3899018
user3899018

Reputation: 1

Get content of row & col from other worksheet no VBA

I have been trying to do this for ages with no success. Searched the net forever.

I have a 54 page workbook, sheet 1 is "Home", 2 is Week1 thru to 54 which is week 53. On Home is a column with 26 entries A3 thru 28, and H 3 thru 29. These have an integer value which can change over the year. Currently it is 14 but it could change on week 30 to 15 etc.

On each week sheet I need to get that value into a cell, D12. Currently I have copied Sheet1!$A3 to the whole book and modified each entry - laborious. I have tried Indirect, Index, Vlookup but every time I get either !Value error or text of the location on sheet1 or the formula error message box!

I cannot find a way to auto increment the reference from sheet to sheet.

D12 on Sheet2 needs to reference Home!$A3, Sheet3 Home!$A4, Sheet4 Home!$A5, etc.

Is there a way to use the current sheet value and use that for the value read into D12 from sheet1 some thing like this:

=Sheet1!$A$(sheet() + 1) for the first 26 entries, then the same with =Sheet1!$H$(sheet() + 1) for the rest.

This would then hopefully read the value from the correct column / row on Home.That code gives a Formula error.

I have tried getting the sheet number value into a spare cell C19 on the current sheet and then using:

Sheet1!$A$C19 or Sheet1!$A$(C19) both of which give the formula error message box.

(C19 = sheet() + 1 gives the correct value for the row I want to access on Home.)

Hopefully this is summed up below:

Cell D12 on sheets 2 thru 26 need to reference Sheet1 A3 thru A28 and get the value of the cell's content. Cell D12 on sheets 27 thru 54 need to reference H3 thru H29 and get the value of the cell's content. Cell C19 on sheets 2 thru 54 can have the index for the row part of the reference by using =(Sheet()) + 1

Is this possible without resorting to VBA?

If not, is it possible to make the value auto update say on closing the file or when the cell is accessed or enter pressed to change the value on Home!$AX?

It sounds simple but I am losing the plot over this one.

Any help would be gratefully received.

Upvotes: 0

Views: 95

Answers (1)

Bmo
Bmo

Reputation: 1212

If your sheets are of the default index (Sheet1, Sheet2, ... SheetN)

You can use the =INDIRECT() function to create a text string of the reference.

=INDIRECT("Sheet" & A1 & "!A1")

If Home!A1 has the value 5, the formula will evaluate to Sheet5!A1 and give you that value.

Indirects are tricky to debug, but you can get a lot of the functionality you are looking for out of it.

Upvotes: 1

Related Questions