PDA

View Full Version : Use of Indirect or other method to pass data between sheets



mexmike
08-08-2015, 02:18 PM
Greetings to All,

Regarding the use of Indirect,

Please excuse my difficulty in explaining exactly what I'm trying to achieve. I have uploaded a vehicle spreadsheet with some comments that may shed some additional light. Apologies in advance if the sheet is protected or read only for any reason.

I need to be able to populate a new sheet with data and receive data from the newly created sheet to a 'Main' sheet. The new sheet is referenced on a 'Main' sheet.

I would like to do this by copying and pasting a formula in columns 'F' and 'G' on multiple rows to allow for new entries on the Main sheet and column 'G' on the new sheets.

The name of the new vehicle/sheet will be typed in column 'C' (Vehicle Reg) on the Main sheet. Columns 'F' (Hours to Next Check) and 'G' (Days to Next Check), previously populated and copied and pasted down with the 'desired formula', would detect the not blank cell and receive the data from the newly named vehicle/sheet. Similarly, the newly created sheet would have a formula to receive its basic data (running hours) from the Main sheet.

The new vehicle cells 'Hours to Next Check' and 'Days to Next Check' on the Main sheet should remain blank if no data is present in the adjacent Vehicle 'Reg Cell'.

I have tried to use the indirect method to no avail due to not knowing how to include a reference to an unknown string in column 'C'.

As previous, your assistance is greatly appreciated.

mexmike
08-09-2015, 05:20 PM
Looks like I have found part of the answer to my Indirect problem by using an * (asterisk).:devil2: Also, using ISBLANK avoids the #REF if a 'Vehicle Reg' cell has not yet been populated. Copying the formula down the column appears to work f.

To reference an unknown sheet name, the asterisk
"'*" in the first part of the formula allows for any sheet name to be typed into C6 for example. Typing a different name in cell C6 for example, makes the formula look at the sheet with that name.

Hours to Next Check

=IF(ISBLANK(C6),"",INDIRECT("'*"&C6&"*'!H2")) and

Days to Next Check

=IF(ISBLANK(C6),"",INDIRECT("'*"&C6&"*'!I2"))

Using an * (asterisk) allowed me to not have to name the sheet at the beginning of the statement. Well, that's my theory anyway. I'm probable right for the wrong reasons but it works.

Now all I have to figure out is how to pass the Running Hours to each newly created sheet from 'Main' in a similar manner that allows a newly created sheet to automatically find the row that contains its Tab/Sheet name on the 'Main' page. Kind of like waiting for a baby to be baptized but still having a way to reference IT:wot