Referincing cell inside of formula

**alekoo** · 09-21-2021, 02:00 PM

Hello guys,

So I'm trying to change a formula based on anther cell and It's driving me crazy. I'll give you an example to make it clear:

I have 3 Sheets, 1 through 3, in both Sheets 1 and 2 I have information, on Sheet3 I'm trying to make a dynamic way of pulling that information. For example cell A1 in Sheet3 is a dropdown list with the selections Sheet1 and Sheet2 - in cell A2 i want to insert a formula that changes depending on what I select on A1 -

for example a sum =sum(Sheet1!A:A,B2:B200) but instead of hard writing Sheet1 I want to be able to reference cell A1 for the first part of the range (Sheet1 or 2)

Would appreciate some suggestions. Thanks!

**Bilb0_** · 09-21-2021, 02:24 PM

Here's a sample I think is what you're looking for.

Edit (9/23/21):
In summary, I created all 3 sheets as you described.
Data on 2 sheets and formulas on the 3rd.

On the 3rd sheet, I created cells for you to specify 2 values for criteria and embellished then with data validation.
Though you did not specify this, it seems important to add this since your formula's proper evaluation would require accuracy in the sheet name selected.

As for the actual formula, I used a VLOOKUP with the function operative to your request (INDIRECT) nested within.
Although you used SUM in your OP, I thought that the VLOOKUP would still provide the necessary function (INDIRECT) and show a little more detail about how to concatenate a full reference inside it.

Please Login or Register  to view this content.

**GeoffW283** · 09-21-2021, 02:50 PM

Welcome to the forum - both of you

Here's a different interpretation:

In sheet3!A2

Formula:

Please Login or Register  to view this content.

See the attached workbook

**AliGW** · 09-21-2021, 05:27 PM

Originally Posted by Bilb0_

Here's a sample I think is what you're looking for.

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

**alekoo** · 09-22-2021, 01:33 AM

Hello, that's it, I've been trying with indirect but must have made a mistake somewhere.

So the way to reference a cell from another sheet is =INDIRECT($C$4&"!h2") --- where C4 is the cell that states in which sheet the information will be looked in and H2 is the cell from that sheet that needs to be returned. Which is what I needed.

And a follow up question, is there a way to increment this formula so that (in my example) the next formula would be =INDIRECT($C$4&"!h3") -- h4, h5 etc.? If I lose the $ the C cell gets incremented, but not the H one.

Thank you guys

**JohnTopley** · 09-22-2021, 01:43 AM

Try

=INDIRECT($C$4&"!"&CELL("address",H3))

**alekoo** · 09-22-2021, 02:13 AM

Lifesaver! Huge thanks!

**AliGW** · 09-22-2021, 02:39 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**Bilb0_** · 09-23-2021, 09:38 AM

I apologize for my oversight.

**AliGW** · 09-28-2021, 03:20 AM

The follow-on question was significantly different and should have been in a new thread. You will find it here: https://www.excelforum.com/excel-for...-criteria.html

This thread will be marked as solved.

**alekoo** · 09-28-2021, 03:33 AM

Did not think it could be anything else than countif. Thanks you!

**AliGW** · 09-28-2021, 03:36 AM

Precisely - completely different to this thread. It's one thread per issue here - please remember this in future.

Referincing cell inside of formula

LinkBack

Thread Tools

Rate This Thread

Display

Referincing cell inside of formula

Re: Referincing cell inside of formula

Re: Referincing cell inside of formula

Re: Referincing cell inside of formula

Re: Referincing cell inside of formula

Re: Referincing cell inside of formula

Re: Referincing cell inside of formula

Re: Referincing cell inside of formula

Re: Referincing cell inside of formula

Re: Referincing cell inside of formula

Re: Referincing cell inside of formula

Re: Referincing cell inside of formula

Thread Information

Users Browsing this Thread

Similar Threads

referincing another workbook using data from within the current workbook

[SOLVED] formula to extract max range used inside another formula and display in another cell

Formula VBA inside corresponding cell, help?

[SOLVED] Formula for 1 Cell Referencing 8 others with format separating each one inside the cell

Replace tab value inside formula with cell value

Change Cell Ref inside a formula

Using a value from a cell inside a formula!

Bookmarks

Bookmarks

Posting Permissions