# cross page excel forumlas

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.

-

## migrated from stackoverflow.comMay 1 '12 at 4:14

This question came from our site for professional and enthusiast programmers.

In A4 on sheet1: `=IF(G4<>"",Sheet2!A4,"")` – Tim Williams May 1 '12 at 0:45
Voted to close question but the comment from Tim Williams is clearly superior to the first answer – brettdj May 1 '12 at 3:45

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!

-
Thanks for the detailed answer! You explained it so well, and I was able to get it working very quickly. Wish I could upvote 5 times! – LordZardeck May 2 '12 at 21:17
sorry about not accepting this! i completely forgot i hadn't – LordZardeck May 28 '12 at 17:47
Thank you, LordZardeck! – rishimaharaj May 29 '12 at 12:19

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.

-
I'm not sure why this has be upvoted given this does not appear to be an answer. The question did not ask to search a closed workbook, and `VLOOKUP` also appears out of scope. – brettdj May 1 '12 at 3:44

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.

-