Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com May 1 '12 at 4:14

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

3  
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

3 Answers 3

up vote 1 down vote accepted

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)

Sheet2 Formulas

(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))

When G4 is Blank test

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

When G4 is Full test

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.

Pulldown & final results

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

Hope this helps!

share|improve this answer
    
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.

share|improve this answer
1  
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.