cross page excel forumlas

Question

I can't seem to get the right lingo to search for the answer on the internet. How can I achieve this setup:

With 2 or more worksheets in 1 workbook.

1. I want to link cells A4, C4 or D4, from Sheet1 to A4, C4 or D4 in Sheet 2 BUT ONLY if: Cell G of Sheet1 has a value (a dollar amount in it) and if it does not have a value in it - nothing happens.

2. I have linked one cell between sheets before but I wanted to do the above. It is my intention for a person to fill out one sheet and then it automatically links to other named sheets with pertinent cells being automatically filled in.

Hope I am making myself clear.

Answer 1

This solution will put the value of A4, C4, and D4 in Sheet1 into A4, C4, and D4 in Sheet2 if the value of G4 in Sheet1 is not blank. We will then copy the formula down so that the same logic applies to A5, C5, D5 (based on G5), etc.

First, put the following into Sheet2 (in A4, C4, and D4):

=IF(ISBLANK(Sheet1!$G4),"",Sheet1!A4)
=IF(ISBLANK(Sheet1!$G4),"",Sheet1!C4)
=IF(ISBLANK(Sheet1!$G4),"",Sheet1!D4)

(Note: In the picture above, I entered the view to see the formulas in the cells by pressing Ctrl-` (the key to the left of 1 on the first row of most keyboards))

Above shows that when G4 in Sheet1 is blank, everything in Sheet2 is blank.

Above shows that when G4 in Sheet1 is full, A4,C4,D4 in Sheet2 are copied from Sheet1.

Click on A4 in Sheet2 and drag the black square in the bottom right corner down to copy the formula to all the cells needed.

Highlight both cells C4 and D4 and drag the black square down to fill the formula in all of those cells as well.

Above shows how to pull down the formulas and the final results.

Hope this helps!

Answer 2

Look into Vlook formula.

=VLOOKUP(A4,'C:\DESKTOP\[YOUREXCELFILENAME.xlsx]Sheet1'!$A$1:$DJ$5000,1,FALSE)


=if(OR(ISNA(cellG), ISBLANK(cellG))," ",VLOOKUP(A4))
[if(logic, true, false)]

if the cell is either N/A or blank then return blank else return value.

Answer 3

Maybe reading to much into your question, but I would suggest that you would be best using the following formula in G4 of Sheet2:

=IF(CELL("type",Sheet1!G4)="v",Sheet1!G4,"")

This ensures that only 'values' (numbers) in Sheet1!G4 populate Sheet2!G4 and not users adding "N/A" (or any text) for example.

Then link your other cells in Sheet2 (A4 etc.) to G4 in Sheet2. An example for A4 would be:

=IF(G4="","",Sheet1!A4)

This second step is similar to Rishi's post but cuts down the links between sheets.