How to change the source of a cell by changing the value of another cell

**bssol** · 01-11-2013, 11:49 AM

Dears All,

I tried to Google and search for and answer with no luck. Please help!

Please see an example of the problem I'm having in this excel file with the explanation inside

http://uploading.com/e4f2c299/exampl...-question-xlsx

I basically want the source of the data in a cell to be changed once another cell value is changed. I tried multiple formulas with no luck as well.

Regards,

**FDibbins** · 01-11-2013, 11:53 AM

Hi bssol and welcome to the forum

THis is probably way too simple for what you want, but have you tried this? Assuming the number that you want to automatically change is in A1, and the number you want to use to make that number change is in B1...
in A1 put =B1

you can upload your file here...
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**bssol** · 01-11-2013, 01:08 PM

Yes you are correct, what you recommended is not what I'm looking for.

I think the attached spredsheet will explain my request better.

Thank you for your advise.

**MrShorty** · 01-11-2013, 01:20 PM

It looks to me like you want the HLOOKUP() function http://office.microsoft.com/en-us/ex...in=HA102752948

**Yaman74** · 01-11-2013, 01:41 PM

This may help, if it is only few variables as given in the sheet.

=IF($B$4=1,E10,IF($B$4=2,F10,IF($B$4=3,G10,IF($B$4=4,H10,I10)))) and then drag (copy) the cells in the column.

The function "INDIRECT" combined with IF function can also help.

**bssol** · 01-11-2013, 02:17 PM

Originally Posted by Yaman74

This may help, if it is only few variables as given in the sheet.

=IF($B$4=1,E10,IF($B$4=2,F10,IF($B$4=3,G10,IF($B$4=4,H10,I10)))) and then drag (copy) the cells in the column.

The function "INDIRECT" combined with IF function can also help.

Yes I thought about it already but because I'll have all 12 months it won't work. I have heard that with VB codes you can make 12 nested if function. but I don't use VB codes.

I'm sure there could be a trick to get the same results with all the lookup functions out there. I'll try HLOOKUP()

Any more help?

**FDibbins** · 01-11-2013, 02:29 PM

in B10, copied down, try this...

=INDEX($E$8:$I$13,ROW(A1)+2,MATCH($B$4,$E$8:$I$8,0))

Note, if you add/subtract any rows between 8 and 10, adjust the row(A1)+2 accordingly

**bssol** · 01-11-2013, 04:36 PM

Originally Posted by FDibbins

in B10, copied down, try this...

=INDEX($E$8:$I$13,ROW(A1)+2,MATCH($B$4,$E$8:$I$8,0))

Note, if you add/subtract any rows between 8 and 10, adjust the row(A1)+2 accordingly

Thank you dear, I was trying to asleep when it just hit me! I just needed to use the MAX function because the numbers are increasing each month and I what i wanted is to bring the last month value into the cell. WOW. I'm so in shock right now!

**FDibbins** · 01-11-2013, 11:21 PM

Im happy you managed to solve your question (although you didnt mention max before lol). Did you try my suggestion though? using max will restrict you to the latest month, with my suggestion, you have the option to pull out any month you want